package com.altocloud.queries;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.altocloud.database.DbUtil;

public class Level3 {
	DbUtil db = new DbUtil();

	Connection conn = db.getDbConnection("226-Level-3");
	Statement stmt = null;
	ResultSet rs = null;
	String sql = "";

	public double findAverageTempByState(String state) {

		double avg = 0.0;
		double total = 0.0;
		int count = 0;

		try {
			stmt = conn.createStatement();
			sql = "select w.tmpf from weather w, station_details sde where w.stn_id_stn=sde.stn and sde.state='"
					+ state + "' and w.tmpf<>-9999";
			System.out.println(sql);

			rs = stmt.executeQuery(sql);
			rs.next();

			avg = rs.getDouble(1);
			System.out.println(avg);

		} catch (Exception e) {
			e.printStackTrace();
		}

		return avg;
	}

	public double findAverageTempByMnet(int mnet) {

		double avg = 0.0;
		double total = 0.0;
		int count = 0;

		try {
			stmt = conn.createStatement();
			sql = "select * from weather w inner join station_details s on w.stn_id_stn=s.stn where s.networkdetails_network_id='"
					+ mnet + "' and w.tmpf<>-9999";
			System.out.println(sql);
			rs = stmt.executeQuery(sql);

		} catch (Exception e) {
			e.printStackTrace();
		}

		try {

			while (rs.next()) {

				double temp = rs.getDouble("tmpf");
				// System.out.println(temp);
				count++;
				total = total + temp;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block

			e.printStackTrace();
		}

		avg = total / count;
		return avg;
	}

	public String HottestState(int month) {

		double avg = 0.0;
		double total = 0.0;
		int count = 0;

		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			sql = "select s.state, avg(w.tmpf) from station_details s inner join weather w on w.stn_id_stn=s.stn where w.tmpf<>-9999 "
					+ "group by s.state order by avg(w.TMPF) desc";
			System.out.println(sql);
			rs = stmt.executeQuery(sql);

		} catch (Exception e) {
			e.printStackTrace();
		}

		String state1 = "";
		try {
			rs.first();
			state1 = rs.getString("state");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return state1;
	}

	public void findPopularProvider() {
		String popular = "";
		try {
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			sql = "select s.priprodetails_provider_id,  count(*) from stn_details_extra s group by s.priprodetails_provider_id order by count(*) desc";
			System.out.println(sql);
			rs = stmt.executeQuery(sql);

		} catch (Exception e) {
			e.printStackTrace();
		}

		try {
			rs.first();
			popular = rs.getString("priprodetails_provider_id");
			System.out.println(popular);
		} catch (Exception e) {

			// TODO Auto-generated catch block
			e.printStackTrace();

		}

	}

	public void findMinMaxTemp() {
		try {
			stmt = conn.createStatement();
			sql = "select max(w.tmpf), min(w.tmpf), sd.state from weather w,"
					+ "station_details sd where w.stn_id_stn=sd.stn and "
					+ "w.tmpf<>-9999 and to_char(w.date, 'yyyy-mm-dd')='2012-10-01' group by sd.state";

			System.out.println(sql);
			rs = stmt.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void delete() {
		try {
			stmt = conn.createStatement();
			sql = "delete from weather where weather_id=12351";
			System.out.println(sql);
			rs = stmt.executeQuery(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String args[]) {
		Level3 l1 = new Level3();
		Long start = System.currentTimeMillis();
		l1.findAverageTempByState("UT");
		l1.HottestState(10);
		l1.findAverageTempByMnet(8);
		l1.findPopularProvider();
		l1.findMinMaxTemp();
		Long stop = System.currentTimeMillis();
		System.out.println("time is : " + (stop - start));
	}

}
